/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package org.plotnikov.pricemaker.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.plotnikov.pricemaker.error.DBException;
import org.plotnikov.pricemaker.error.RootException;
import org.plotnikov.pricemaker.utils.DBEntity;

/**
 *
 * @author alexey
 */
public class SQLiteDBOperationsImpl implements DBOperations{

    private Connection conn = null;

    public SQLiteDBOperationsImpl() throws DBException{
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
            throw new DBException(ex.getMessage());
        }
        try {
            conn = DriverManager.getConnection("jdbc:sqlite:test.db");

            init();

            instance = this;
        } catch (SQLException ex) {
            Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
            throw new DBException(ex.getMessage());
        }

    }

    private void init() throws SQLException {
        Statement stat = null;
        try{
            stat = conn.createStatement();
            stat.executeUpdate("create table if not exists organisation " +
                    "(id integer primary key autoincrement," +
                    " name text)");

            stat.executeUpdate("create table if not exists production_type " +
                    "(id integer primary key autoincrement," +
                    " name text)");

            stat.executeUpdate("create table if not exists production " +
                    "(id integer primary key autoincrement," +
                    "id_organisation integer," +
                    "id_production_type integer," +
                    "name text," +
                    "foreign key (id_organisation) references organisation(id)," +
                    "foreign key (id_production_type) references production_type(id)" +
                    ")");

            stat.executeUpdate("create table if not exists price_for " +
                    "(id integer primary key autoincrement," +
                    " name text)");

            stat.executeUpdate("create table if not exists postavchiki " +
                    "(id integer primary key autoincrement," +
                    " name text)");

            stat.executeUpdate("create table if not exists pricelist " +
                    "(id integer primary key autoincrement," +
                    "id_production integer," +
                    "id_postavchik integer," +
                    "id_price_for integer,"+
                    "rubl integer," +
                    "copeek varchar(5)," +
                    "price_date date," +
                    "percent integer," +
                    "other_information varchar(255)," +
                    "positions_xml text," +
                    "foreign key (id_production) references production(id)," +
                    "foreign key (id_postavchik) references postavchiki(id)," +
                    "foreign key (id_price_for) references price_for(id)"+
                    ")");
        }finally{
            if (stat!=null)
                stat.close();
        }

    }

    public void initDB() throws RootException {
        try {
            Statement stat = conn.createStatement();
            stat.executeUpdate("");
        } catch (SQLException ex) {
            Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public List<Object[]> getListPrices() throws RootException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    private static SQLiteDBOperationsImpl instance = null;

    public static SQLiteDBOperationsImpl getInstance() throws DBException{
        if (instance == null){
            instance = new SQLiteDBOperationsImpl();
        }
        return instance;
    }

    public void test1() {
        try {
            Statement stat = conn.createStatement();
            stat.executeUpdate("create table if not exists test_table " +
                    "(id integer primary key autoincrement," +
                    " name text)");
            ResultSet rs = stat.executeQuery("select max(id)+1 from test_table");
            int id = rs.getInt(1);
            if (id==0)
                ++id;
            System.out.println("id="+id);
            PreparedStatement st = conn.prepareStatement("insert into test_table values (" +
                    "?," +
                    "'text1')");
            st.setInt(1, id);
            st.execute();
            System.out.println("test1 executed successfully");
        } catch (SQLException ex) {
            Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void test2() {
        try {
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("select * from test_table;");
            while (rs.next()){
                System.out.println(rs.getInt("id")+":"+rs.getString("name"));
            }
            System.out.println("test2 executed successfully");
        } catch (SQLException ex) {
            Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void makeAddAction(String tablename, String name) throws DBException{
        PreparedStatement st = null;
        try{
//            st = conn.prepareStatement("insert into "+tablename+" (name) values (?)");
//            st.setString(1, name);
            st = conn.prepareStatement("insert into "+tablename+" (name) values ('"+name+"')");
            st.execute();
        }catch (Throwable t){
            t.printStackTrace();
            throw new DBException(t.getMessage());
        }finally{
            if (st!=null)
                try {
                    st.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
        }
    }

    public void addOrganisation(String name) throws DBException {
        makeAddAction("organisation", name);
    }

    private void makeUpdateAction(String tablename, Integer id, String name) throws DBException {
        PreparedStatement st = null;
        try{
//            st = conn.prepareStatement("update "+tablename+" set name=? where id=?");
//            st.setString(1, name);
//            st.setInt(2, id);
            st = conn.prepareStatement("update "+tablename+" set name='"+name+"' where id="+id);
            st.execute();
        }catch (Throwable t){
            t.printStackTrace();
            throw new DBException(t.getMessage());
        }finally{
            if (st!=null)
                try {
                    st.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
        }
    }

    public void updateOrganisation(Integer id, String name) throws DBException {
        makeUpdateAction("organisation", id, name);
    }

    private void makeDeleteAction(String tablename, Integer id) throws DBException{
        PreparedStatement st = null;
        try{
//            st = conn.prepareStatement("delete from "+tablename+" where id=?");
//            st.setInt(1, id);
            st = conn.prepareStatement("delete from "+tablename+" where id="+id);
            st.execute();
        }catch (Throwable t){
            t.printStackTrace();
            throw new DBException(t.getMessage());
        }finally{
            if (st!=null)
                try {
                    st.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
        }
    }

    public void deleteOrganisation(Integer id) throws DBException {
        makeDeleteAction("organisation", id);
    }

    private Vector<DBEntity> makeListAction(String tablename) throws DBException{
        return makeListAction(tablename, "");
    }

    private Vector<DBEntity> makeListAction(String tablename, String querystr) throws DBException{
        Statement stat = null;
        ResultSet rs = null;
        try{
            Vector<DBEntity> data = new Vector<DBEntity>();
            stat = conn.createStatement();
            rs = stat.executeQuery("select * from "+tablename+" "+querystr+" order by name;");
            while (rs.next()) {
                DBEntity e = new DBEntity();
                e.setField("id", new Integer(rs.getInt("id")));
                e.setField("name", rs.getString("name"));
                data.add(e);
            }
            return data;
        }catch (Throwable e){
            e.printStackTrace();
            throw new DBException(e.getMessage());
        }finally{
            if (rs != null){
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (stat != null){
                try {
                    stat.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    public Vector<DBEntity> listOrganisations() throws DBException {
        return makeListAction("organisation");
    }

    public void addTitleProduction(String name) throws DBException {
        makeAddAction("production_type", name);
    }

    public void updateTitleProduction(Integer id, String name) throws DBException {
        makeUpdateAction("production_type", id, name);
    }

    public void deleteTitleProduction(Integer id) throws DBException {
        makeDeleteAction("production_type", id);
    }

    public Vector<DBEntity> listTitleProducts() throws DBException {
        return makeListAction("production_type");
    }

    public void addProduction(Integer id_org, Integer id_type, String name) throws DBException {
        PreparedStatement st = null;
        try{
            st = conn.prepareStatement("insert into production (id_organisation," +
                    "id_production_type," +
                    "name) values ("+id_org+","+id_type+",'"+name+"')");
//            st.setInt(1, id_org);
//            st.setInt(2, id_type);
//            st.setString(3, name);
            st.execute();
        }catch (Throwable t){
            t.printStackTrace();
            throw new DBException(t.getMessage());
        }finally{
            if (st!=null)
                try {
                    st.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
        }
    }

    public void updateProduction(Integer id, String name) throws DBException {
        makeUpdateAction("production", id, name);
    }

    public void deleteProduction(Integer id) throws DBException {
        makeDeleteAction("production", id);
    }

    public Vector<DBEntity> listProducts(Integer id_org, Integer id_type) throws DBException {
        return makeListAction("production", "where id_organisation="+id_org+" and " +
                "id_production_type="+id_type);
    }

    public void addPostavchik(String name) throws DBException {
        makeAddAction("postavchiki", name);
    }

    public void updatePostavchik(Integer id, String name) throws DBException {
        makeUpdateAction("postavchiki", id, name);
    }

    public void deletePostavchik(Integer id) throws DBException {
        makeDeleteAction("postavchiki", id);
    }

    public Vector<DBEntity> listPostavchiki() throws DBException {
        return makeListAction("postavchiki");
    }

    public void addPriceFor(String name) throws DBException {
        makeAddAction("price_for", name);
    }

    public void updatePriceFor(Integer id, String name) throws DBException {
        makeUpdateAction("price_for", id, name);
    }

    public void deletePriceFor(Integer id) throws DBException {
        makeDeleteAction("price_for", id);
    }

    public Vector<DBEntity> listPricesFor() throws DBException {
        return makeListAction("price_for");
    }

    public Vector<DBEntity> searchPriceLists(String search) throws DBException {

        Statement stat = null;
        ResultSet rs = null;
        try{
            Vector<DBEntity> data = new Vector<DBEntity>();
            stat = conn.createStatement();
            rs = stat.executeQuery("select price.id, strftime('%d',price.price_date)||'.'||" +
                    "strftime('%m',price.price_date)||'.'||" +
                    "strftime('%Y',price.price_date), o.name, " +
                    "pt.name, prod.name, price.other_information," +
                    "post.name, price.rubl, price.copeek, pf.name " +
                    "from pricelist price, postavchiki post," +
                    "production prod, production_type pt, organisation o, price_for pf " +
                    "where " +
                    "price.id_production=prod.id and " +
                    "price.id_postavchik=post.id and " +
                    "prod.id_production_type=pt.id and " +
                    "prod.id_organisation=o.id and " +
                    "pf.id=price.id_price_for and " +
                    "(price.other_information like '%"+search+"%' or " +
                    "prod.name like '%"+search+"%' or " +
                    "pt.name like '%"+search+"%' or " +
                    "o.name like '%"+search+"%' or "+
                    "pf.name like '%"+search+"%' or "+
                    "price.rubl like '%"+search+"%' or "+
                    "price.copeek like '%"+search+"%' or "+
                    "strftime('%d',price.price_date)||'.'||" +
                    "strftime('%m',price.price_date)||'.'||" +
                    "strftime('%Y',price.price_date) like '%"+search+"%' or "+
                    "post.name like '%"+search+"%');");

            while (rs.next()) {
                DBEntity e = new DBEntity();
                e.setField("id", new Integer(rs.getInt(1)));
                String tmp = rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4)+" "+
                        rs.getString(5)+" "+rs.getString(6)+" "+rs.getString(7)+" "+
                        rs.getString(8)+" руб. "+rs.getString(9)+" коп. "+rs.getString(10);
                e.setField("name", tmp);
                data.add(e);
            }
            return data;
        }catch (Throwable e){
            e.printStackTrace();
            throw new DBException(e.getMessage());
        }finally{
            if (rs != null){
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (stat != null){
                try {
                    stat.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    public void addPricer(DBEntity e) throws DBException {
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            st = conn.prepareStatement("insert into pricelist (" +
                    "id_production," +
                    "id_postavchik," +
                    "rubl," +
                    "copeek," +
                    "price_date," +
                    "percent," +
                    "other_information," +
                    "positions_xml," +
                    "id_price_for) values ("+e.getInt("id_prod")+"," +
                    e.getInt("id_post")+"," +
                    e.getInt("rubl")+"," +
                    "'"+e.getString("copeek")+"',"+
                    "'"+df.format(e.getObject("date", java.util.Date.class))+"',"+
                    "0,"+
                    "'"+e.getString("other")+"',"+
                    "'"+e.getString("xml")+"',"+
                    e.getString("id_price_for")+
                    ")");

//            st.setInt(1, e.getInt("id_prod"));
//            st.setInt(2, e.getInt("id_post"));
//            st.setInt(3, e.getInt("rubl"));
//            st.setString(4, e.getString("copeek"));
//            st.setString(5, df.format(e.getObject("date", java.util.Date.class)));
//            st.setInt(6, 0);
//            st.setString(7, e.getString("other"));
//            st.setString(8, e.getString("xml"));
//            st.setString(9, e.getString("id_price_for"));
//            st.set
            st.execute();
        }catch (Throwable t){
            t.printStackTrace();
            throw new DBException(t.getMessage());
        }finally{
            if (st!=null)
                try {
                    st.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            if (rs != null){
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    public DBEntity getPricerObj(int id) throws DBException {
        Statement stat = null;
        ResultSet rs = null;
        try{
            DBEntity e = new DBEntity();
            stat = conn.createStatement();
            rs = stat.executeQuery("select prod.id_organisation, prod.id_production_type, " +
                    "pl.id_production, pl.id_postavchik, pl.rubl, pl.copeek, pl.price_date, " +
                    "pl.percent, pl.other_information, pl.positions_xml, pl.id_price_for " +
                    "from pricelist pl, postavchiki post," +
                    "price_for pf, production prod " +
                    "where " +
                    "pl.id="+id+
                    " and pl.id_postavchik=post.id and " +
                    "pl.id_price_for=pf.id and " +
                    "pl.id_production=prod.id");

            while (rs.next()) {
                e.setField("id", id);

                e.setField("id_organisation", rs.getInt(1));
                e.setField("id_production_type", rs.getInt(2));

                e.setField("id_production", rs.getInt(3));
                e.setField("id_postavchik", rs.getInt(4));
                e.setField("rubl", rs.getInt(5));
                e.setField("copeek", rs.getString(6));
                e.setField("price_date", rs.getString(7));
                e.setField("percent", rs.getInt(8));
                e.setField("other_information", rs.getString(9));
                e.setField("xml", rs.getString(10));
                e.setField("id_price_for", rs.getInt(11));
            }
            return e;
        }catch (Throwable e){
            e.printStackTrace();
            throw new DBException(e.getMessage());
        }finally{
            if (rs != null){
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (stat != null){
                try {
                    stat.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    public final static SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    public void updatePricer(DBEntity e) throws DBException {
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            st = conn.prepareStatement("update pricelist " +
                    "set id_production="+e.getInt("id_prod")+", id_postavchik="+e.getInt("id_post")+", " +
                    "rubl="+e.getInt("rubl")+", " + "copeek='"+e.getString("copeek")+"', " +
                    "percent=0, " + "other_information='"+e.getString("other")+"', " +
                    "positions_xml='"+e.getString("xml")+"'," + "id_price_for="+e.getString("id_price_for")+"," +
                    "price_date='"+df.format(e.getObject("date", java.util.Date.class))+"'"+
                    " where id="+e.getInt("id")
                    );
//            st.setInt(1, e.getInt("id_prod"));
//            st.setInt(2, e.getInt("id_post"));
//            st.setInt(3, e.getInt("rubl"));
//            st.setString(4, e.getString("copeek"));
//            st.setInt(5, 0);
//            st.setString(6, e.getString("other"));
//            st.setString(7, e.getString("xml"));
//            st.setString(8, e.getString("id_price_for"));
//            st.setString(9, df.format(e.getObject("date", java.util.Date.class)));
            st.execute();
        }catch (Throwable t){
            t.printStackTrace();
            throw new DBException(t.getMessage());
        }finally{
            if (st!=null)
                try {
                    st.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            if (rs != null){
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    public void deletePricers(int[] ids) throws DBException {
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            for (int i = 0; i < ids.length; i++) {
                st = conn.prepareStatement("delete from pricelist where id="+ids[i]);
                st.execute();
            }

            
        }catch (Throwable t){
            t.printStackTrace();
            throw new DBException(t.getMessage());
        }finally{
            if (st!=null)
                try {
                    st.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            if (rs != null){
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(SQLiteDBOperationsImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    }
